package sheet

import (
	"fmt"
	"github.com/goravel/framework/facades"
	"github.com/goravel/framework/support/path"
	"github.com/spf13/cast"
	"github.com/xuri/excelize/v2"
	"goravel/app/models"
	"os"
	"strconv"
)

// 导入表服务
type SheetService struct {
}

func NewSheetService() *SheetService {
	return &SheetService{}
}

func (e *SheetService) FillRule(name string) error {
	query := facades.Orm().Query()
	truncateSql := "truncate table manhours"
	query.Exec(truncateSql)
	filepath := fmt.Sprintf("%s/%s", path.Public("sheet"), name)
	//读取文件内容
	file, err := os.Open(filepath)
	if err != nil {
		return err
	}
	f, err := excelize.OpenReader(file)
	defer f.Close()
	if err != nil {
		return err
	}
	//获取第一个sheet
	sheetName := f.GetSheetName(0)

	// 获取表头数据
	headerLen := 14
	headerRows, err := f.GetRows(sheetName)
	headerRow := headerRows[0]
	// 从第二行开始读取数据（因为第一行是表头）
	rows, err := f.GetRows(sheetName)
	// 解析Excel数据并插入到数据库
	for rowIndex, row := range rows {
		if rowIndex == 0 {
			// 跳过第一行表头
			continue
		}
		serviceID, err := strconv.ParseInt(row[0], 10, 64)
		if err != nil {
			fmt.Println("解析service_id失败:", err)
			continue
		}
		// 判断当前行长度是否小于表头长度，如果是则扩充当前行长度至与表头一致
		if len(row) < headerLen {
			for i := len(row); i < headerLen; i++ {
				row = append(row, "")
			}
		}

		// 确保每一行读取的次数与表头数量一致
		for colIndex, cellValue := range row[1 : headerLen+1] {
			if cellValue == "" {
				// 如果单元格数据为空，忽略该条记录插入
				continue
			}
			kindID := cast.ToInt64(headerRow[colIndex+1])
			score := cast.ToFloat64(cellValue)

			query.Model(&models.Manhour{}).Create(&models.Manhour{
				KindID:    cast.ToUint(kindID),
				ServiceID: cast.ToUint(serviceID),
				Score:     score,
			})
		}
	}
	return nil
}

func (e *SheetService) FillService(name string) error {
	query := facades.Orm().Query()
	truncateSql := "truncate table services"
	query.Exec(truncateSql)
	filepath := fmt.Sprintf("%s/%s", path.Public("sheet"), name)
	//读取文件内容
	file, err := os.Open(filepath)
	if err != nil {
		return err
	}
	f, err := excelize.OpenReader(file)
	defer f.Close()
	if err != nil {
		return err
	}
	//获取第一个sheet
	sheetName := f.GetSheetName(0)
	rows, err := f.GetRows(sheetName)
	// 解析Excel数据并插入到数据库
	for _, row := range rows {
		query.Model(&models.Service{}).Create(&models.Service{
			Name: row[0],
			Code: row[1],
		})
	}
	return nil
}
